﻿using System.Data;
using System.Data.SqlClient;
using Common.Constant;
using log4net;
using Entity;
using Blog.Common.Helpers;

namespace DataAccess
{
    public class CategoryDao
    {
        private static readonly ILog Logger = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        private SqlConnection _sqlCon;
        private DataSet _dataSet;
        public DataSet SelectAllCategory()
        {
            try
            {
                _sqlCon = DBConnection.SqlGetConnection();
                _dataSet = SqlHelper.ExecuteDataSet(_sqlCon, CommandType.StoredProcedure, DBSchemas.GetAllCategory);
                return _dataSet;
            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }

        public DataSet SelectCategoryById(int id)
        {
            try
            {
                var sqlParametter = new SqlParameter[1];
                sqlParametter[0] = new SqlParameter("@CatId",SqlDbType.Int){Value = id};
                _sqlCon = DBConnection.SqlGetConnection();
                _dataSet = SqlHelper.ExecuteDataSet(_sqlCon, CommandType.StoredProcedure, DBSchemas.GetAllCategory);
                return _dataSet;
            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }
        public int AddCategory(Category category)
        {
            try
            {
                var sqlParametters = new SqlParameter[4];
                sqlParametters[0] = new SqlParameter("@CatId", DbType.Int32) { Value = category.CatId };
                sqlParametters[1] = new SqlParameter("@CatName", DbType.String) { Value = category.CatName };
                sqlParametters[2] = new SqlParameter("@CatDescription", DbType.String) { Value = category.CatDescription };
                sqlParametters[3] = new SqlParameter("@PostCount", DbType.DateTime) { Value = category.PostCount};
                _sqlCon = DBConnection.SqlGetConnection();
                return SqlHelper.ExecuteNonQuery(_sqlCon, CommandType.StoredProcedure, DBSchemas.AddCategory, sqlParametters);

            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }
        public int UpdateCategory(Category category)
        {
            try
            {
                var sqlParametters = new SqlParameter[3];
                sqlParametters[0] = new SqlParameter("@CatId", DbType.Int32) { Value = category.CatId };
                sqlParametters[1] = new SqlParameter("@CatName", DbType.String) { Value = category.CatName };
                sqlParametters[2] = new SqlParameter("@CatDescription", DbType.String) { Value = category.CatDescription };
                _sqlCon = DBConnection.SqlGetConnection();
                return SqlHelper.ExecuteNonQuery(_sqlCon, CommandType.StoredProcedure, DBSchemas.UpdateCategory, sqlParametters);

            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }
        public int DeleteCategory(int id)
        {
            try
            {
                var sqlParametters = new SqlParameter[1];
                sqlParametters[0] = new SqlParameter("@CatId", DbType.Int32) { Value = id };
                _sqlCon = DBConnection.SqlGetConnection();
                return SqlHelper.ExecuteNonQuery(_sqlCon, CommandType.StoredProcedure, DBSchemas.DeleteCategory, sqlParametters);

            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }
    }
}
